package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

public abstract class DbAccess<T> {

	private int id;

	public Record<T> find_by_id(String tableName, int id) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Record<T> rec = null;

		String sql = "select * from  " + tableName + " where id = ?";
		try {
			con = DBManager.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				rec = ResultSetToRecord(rs);
			}
			rs.close();
		}
		catch (SQLException e) {
			rs = null;
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {ps.close();}
				catch (SQLException e) {e.printStackTrace();}
			}
			if (con != null) {
				try {con.close();}
				catch (SQLException e) {e.printStackTrace();}
			}
		}
		return rec;
	}

	public int remove_by_id(String tableName, int id) {
		Connection con = null;
		PreparedStatement ps = null;
		int cnt = 0;

		String sql = "delete from " + tableName + " where id = ?";
		try {
			con = DBManager.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			cnt = ps.executeUpdate();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {ps.close();}
				catch (SQLException e) {e.printStackTrace();}
			}
			if (con != null) {
				try {con.close();}
				catch (SQLException e) {e.printStackTrace();}
			}
		}
		return cnt;
	}

	public int add(String tableName, Map<String, String> map) {
		Connection con = null;
		Statement stm = null;
		int cnt = 0;

		StringBuffer sql = new StringBuffer();
		StringBuffer sql_set = new StringBuffer();

		for (String key : map.keySet()) {
			if (sql.length() == 0) {
				sql.append(" insert into " + tableName + "(");
			}
			else {
				sql.append(",");
			}
			sql.append(key);

			if (sql_set.length() == 0) {
				sql_set.append(" values (");
			}
			else {
				sql_set.append(",");
			}
			sql_set.append("'" + map.get(key) + "'");
		}
		if (sql.length() > 0) {
			sql.append(") ");
		}
		if (sql_set.length() > 0) {
			sql_set.append(") ");
		}
		String sql_insert = sql.toString() + sql_set.toString();

		if (sql_insert.length() > 0) {
			try {
				con = DBManager.getConnection();
				stm = con.createStatement();
				cnt = stm.executeUpdate(sql_insert);
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			finally {
				if (stm != null) {
					try {stm.close();}
					catch (SQLException e) {e.printStackTrace();}
				}
				if (con != null) {
					try {con.close();}
					catch (SQLException e) {e.printStackTrace();}
				}
			}
		}
		return cnt;
	}

	public int update(String tableName, Map<String, String> map) {
		Connection con = null;
		Statement stm = null;
		int cnt = 0;

		StringBuffer sql = new StringBuffer();

		for (String key : map.keySet()) {
			if (sql.length() == 0) {
				sql.append(" update " + tableName + " set ");
			}
			else {
				sql.append(",");
			}
			sql.append(key);
			sql.append(" = ");
			sql.append("'" + map.get(key) + "'");
		}
		if (sql.length() > 0) {
			sql.append(" where id = " + map.get("id"));
		}

		if (sql.length() > 0) {
			try {
				con = DBManager.getConnection();
				stm = con.createStatement();
				cnt = stm.executeUpdate(sql.toString());
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			finally {
				if (stm != null) {
					try {stm.close();}
					catch (SQLException e) {e.printStackTrace();}
				}
				if (con != null) {
					try {con.close();}
					catch (SQLException e) {e.printStackTrace();}
				}
			}
		}
		return cnt;
	}

	protected abstract Record<T> ResultSetToRecord(ResultSet rs);
	protected abstract Map<String, String> RecordToMap();

	/**
	 * @return id
	 */
	public int getId() {
		return id;
	}

	/**
	 * @param id セットする id
	 */
	public void setId(int id) {
		this.id = id;
	}
}
